package com.compty.paint.manager;

import java.sql.SQLException;
import java.util.Date;
import java.util.List;

import org.hibernate.Criteria;
import org.hibernate.Hibernate;
import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.compty.core.hibernate.HibernateEntityDao;

@SuppressWarnings("unchecked")
public class AuditManager extends HibernateEntityDao {

	public List pageQueryAll(final Date startDate,final Date endDate,final int curPage, final int pageSize) {

		return  (List) this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				String sql = "select * from (select id,uploadtime,'painting' as flag  from t_painting as a union " +
				" select  id,uploadtime,'painter' as flag  from t_painter as b " +
				" union select  id ,uploadtime,'exhibition' as flag from t_exhibition as c " +
				" union select  id ,uploadtime,'auction' as flag from t_auction as d " +
				" union select  id ,uploadtime,'gallery' as flag from t_gallery as e ) as f  " ;
				
				
				if(startDate != null){
					sql += " where f.uploadtime >= :startDate ";
				}
				
				if(endDate != null){
					if(startDate == null){
						sql += " where f.uploadtime < :endDate ";
					}else{
						sql += " and f.uploadtime < :endDate ";
					}
				}
				
				sql += " order by f.uploadtime desc limit "+ (curPage-1) +","+pageSize; 
				
				SQLQuery query = session.createSQLQuery(sql);
				if(startDate != null){
					query.setParameter("startDate", startDate);
				}
				if(endDate != null){
					query.setParameter("endDate", endDate);
				}
				
				List list = query.addScalar("id", Hibernate.LONG).addScalar("flag", Hibernate.STRING)
				.addScalar("uploadtime",Hibernate.TIMESTAMP).setResultTransformer(Criteria.ALIAS_TO_ENTITY_MAP).list();
				return list;
			}
		});

	}
	
	public int totalCount(final Date startDate,final Date endDate,final int curPage, final int pageSize){
		
		return (Integer) this.getHibernateTemplate().execute(new HibernateCallback() {
			public Object doInHibernate(Session session)
					throws HibernateException, SQLException {
				
				String sql = "select count(*) as totalCount from (select id,uploadtime from t_painting as a union " +
				" select  id,uploadtime from t_painter as b " +
				" union select id,uploadtime from t_exhibition as c " +
				" union select  id ,uploadtime from t_auction as d " +
				" union select  id ,uploadtime from t_gallery as e ) as f ";
				
				if(startDate != null){
					sql += " where f.uploadtime >= :startDate ";
				}
				
				if(endDate != null){
					if(startDate == null){
						sql += " where f.uploadtime < :endDate ";
					}else{
						sql += " and f.uploadtime < :endDate ";
					}
				}
				
				SQLQuery query = session.createSQLQuery(sql);
				if(startDate != null){
					query.setParameter("startDate", startDate);
				}
				if(endDate != null){
					query.setParameter("endDate", endDate);
				}
				
				return query.addScalar("totalCount", Hibernate.INTEGER).uniqueResult();
			}
		});
	}

}
